﻿using System;
using System.Collections.Generic;

using System.Text;
using Portal.Interface;
using Portal.Environment;
using Portal.Language;
using System.Data;
using System.Data.SqlClient;

namespace Portal.CMS
{
    public class LawDocumentOfficeBLL
    {
        public string ConnectionString
        {
            get
            {
                return Environments.ConnectionString;
            }
        }

        public LawDocumentOfficeCls CreateModel(string LawDocumentOfficeId)
        {
            string Query = " select TableLawDocumentOffice.* from TableLawDocumentOffice where LawDocumentOfficeId = '" + LawDocumentOfficeId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(ConnectionString));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            SqlDataReader SqlDataReader = SqlCommand.ExecuteReader();
            if (!SqlDataReader.HasRows)
            {
                throw new Exception("Không tìm thấy bản ghi LawDocumentOfficeId có mã: " + LawDocumentOfficeId);
            }

            SqlDataReader.Read();
            LawDocumentOfficeCls tLawDocumentOfficeCls = new LawDocumentOfficeCls();
            tLawDocumentOfficeCls.LawDocumentOfficeId = SqlDataReader.GetString(SqlDataReader.GetOrdinal("LawDocumentOfficeId"));
            tLawDocumentOfficeCls.Subject = SqlDataReader.GetString(SqlDataReader.GetOrdinal("Subject"));

            return tLawDocumentOfficeCls;
        }



        public DataTable ReadingLawDocumentOffice(string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FuncUtilities.CorrectForSearch(Keyword);
            string Query = "select LawDocumentOfficeId,Subject from TableLawDocumentOffice where 1=1 ";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and Subject like N'%" + Keyword + "%'";
            }
            Query += " order by [Subject]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(ConnectionString));

            da.Fill(dataTable);
            return dataTable;
        }


        public string CreateLawDocumentOffice(
            SqlTransaction _SqlTransaction,
            string Subject)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewLawDocumentOfficeId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Insert into TableLawDocumentOffice(LawDocumentOfficeId,Subject) values (@LawDocumentOfficeId,@Subject)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentOfficeId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentOfficeId"].Value = NewLawDocumentOfficeId;

            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewLawDocumentOfficeId;
        }



        public void UpdateLawDocumentOffice(SqlTransaction _SqlTransaction,
            string LawDocumentOfficeId,
            string Subject)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableLawDocumentOffice set Subject=@Subject where LawDocumentOfficeId=@LawDocumentOfficeId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentOfficeId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentOfficeId"].Value = LawDocumentOfficeId;

            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void DeleteLawDocumentOffice(SqlTransaction _SqlTransaction,
            string LawDocumentOfficeId)
        {
            LawDocumentOfficeCls oLawDocumentOfficeCls = new LawDocumentOfficeBLL().CreateModel(LawDocumentOfficeId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = "Delete TableLawDocumentOffice where LawDocumentOfficeId=@LawDocumentOfficeId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentOfficeId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentOfficeId"].Value = LawDocumentOfficeId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oLawDocumentOfficeCls != null)
                {
                    SubjectDeleted = oLawDocumentOfficeCls.Subject;
                }
                Environments.AddDeletedRecordForSync(_SqlTransaction, "TableLawDocumentOffice","Nơi ban hành văn bản", LawDocumentOfficeId, SubjectDeleted);
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }
    }
}
